Create Materialization Job Dialog

Some items such as tables, views, joins or aggregations can be materialized into the DWH. The materialized tables will be used by our optimizer to rewrite queries and redirect them to the DWH instead of the original data source.

Choose the parameters for a Materialization Job to be created

• Complete load: the job should copy all data from the data sources' tables that are affected by the selected optimization.
• Incremental load: only the newest data (the increment) should be loaded by the job. Helps to save bandwidth and minimize the load on data sources. Currently, only single tables are supported.

Advanced options:

• Parallel runs allowed: the limit of simultaneous job runs: unlimited (-1), job execution not allowed (0)

Complete Load Job

A complete load job may automatically create indexes. This behavior is controlled by a set of radio buttons:

• Create all indexes: will force the server to create all the possible indexes
• Create manually specified indexes: will create only the manually specified indexes
• Do not create indexes: won't create any indexes;

This type of job can trigger native statistics gathering once replication is finished.

Incremental Load Job

An incremental job checks what rows where added using a row check expression or table column. Optionally, it allows to check if old rows where changed to avoid duplications by specifying an identity expression or table column.

New row check

Which rows are new can be calculated by either

• Selecting a field: all rows in a data source's table with the field's value > MAX(field) of the already loaded data will be considered new and transferred to the Analytical Storage
• SQL expression: instead of using a MAX function on a field, you can write a custom expression to determine which rows are new and should be considered as an increment

SQL expression examples

In all examples we assume that the table in the data source has a column called lastTimestamp which contains the timestamp of the last update of the data in the corresponding row:

• max(lastTimestamp) - updates all data starting from the last date/time when the Analytical Storage was updated. Alternatively, you can just select the field lastTimestamp in the dropdown box
• cast(max(lastTimestamp) as date) - updates all data starting from 0:00 on the day the Analytical Storage was last updated
• max(year(lastTimestamp)*100+month(lastTimestamp)) - updates all data starting from 0:00 on the 1st day of the month when the Analytical Storage was last updated
• cast(timestampAdd(SQL_TSI_DAY,-1,max(lastTimestamp)) as date) - updates data starting from 0:00 one day before the Analytical Storage was last updated

Identity Check

In some cases, the original table may have been updated by editing an existing row with a new value. If this new value passes new row check, it will be inserted into the materialized table, breaking its consistency (i.e. a constraint violation because of primary key duplication).

To avoid unnecessary data duplication, the incremental job offers an optional identity check field or expression.

Before inserting a new value, the job will ensure that the value identity field or expression is unique for this row. Otherwise, the existing row will be updated instead. The most practical is to specify a primary key field as identity check to ensure consistency and to avoid duplications.

Delete Old Data

If this option is checked, the incremental job will delete and update all the rows in the materialized table that match the value of the newRowCheckExpression. When this option is unchecked, the incremental job doesn't delete these rows from the materialized table and only adds the new ones.

By default this option is enabled.

Native Statistics Gathering

This type of job can trigger native statistics gathering once replication is finished.

Reference manual pages:

• Jobs and Schedules
  • Creating schedules
  • Chained schedules
  • Managing schedules
  • Managing jobs